package com.sae.vote.util;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class DbUtilsTemplate {

	// 按时间逆序所有的有效的投票主题
	public static final String S1 = "select d.*,u.username from subject d inner join users u on d.creator = u.userID where d.state = 1 order by d.beginTime desc";

	// 通过用户名和密码查找有效用户
	public static final String S2 = "select * from users where username = ? and passwd = ? and state = 1";

	// 用户创建的有效主题
	public static final String S3 = "select * from subject where state = 1 and creator = ? order by beginTime desc";

	// 保存主题
	public static final String S4 = "insert into subject (subjectID, subjectTitle, creator, beginTime, endTime, isAnonym, subjectType, state, descript, cover) values (?,?,?,?,?,?,?,?,?,?)";

	// 保存选项
	public static final String S5 = "insert into items (itemID, subjectID, item, no) values (?,?,?,?)";

	// 获取每题的选项
	public static final String S6 = "select * from items where subjectID = ? order by no";

	// 获得某此投票
	public static final String S7 = "select * from subject where subjectID = ? and state = 1";

	// 判断用户是否对该主题投过票
	public static final String S8 = "select count(1) from voterecord where subjectID = ? and userID = ?";

	// 记录投票
	public static final String S9 = "insert into voterecord values (?,?,?)";

	// 更新得票数
	public static final String S10 = "update items set itemCount = itemCount + 1 where itemID = ?";

	// 获得选项信息
	public static final String S11 = "select * from items where itemID = ?";
	
	public static final String S12 = "select count(1) from users where username = ?";
	
	public static final String S13 = "insert into users (username, passwd, gender, email, state) values(?,?,?,?,?)";
	
	// 每页记录的数量
	public static final int PAGE_SIZE = 15;

	@Autowired
	private DataSource dataSource;

	@Autowired
	private DataSource dataSource2;

	private QueryRunner queryRunner;

	private QueryRunner queryRunner2;

	/**
	 * 返回一个只读的数据库操作工具
	 * 
	 * @return
	 */
	public QueryRunner getWriteRunner() {
		queryRunner = new QueryRunner(dataSource);
		return queryRunner;
	}

	/**
	 * 返回一个只写的数据库操作工具
	 * 
	 * @return
	 */
	public QueryRunner getReadRunner() {
		queryRunner2 = new QueryRunner(dataSource2);
		return queryRunner2;
	}

}
